package com.czjtxy.extraTerm.daoImpl;

import com.czjtxy.extraTerm.dao.TemployeeDao;
import com.czjtxy.extraTerm.entity.DO.Temployee;
import com.czjtxy.extraTerm.entity.VO.TemployeeVO;
import com.czjtxy.extraTerm.util.Util;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.LinkedList;
import java.util.List;

/**
 * @program: staff-house
 * @author: MingYi
 * @create: 2021/07/01 16:11
 */
public class TemployeeDaoImpl extends BaseDaoImpl implements TemployeeDao {
    @Override
    public List<TemployeeVO> getAll() throws SQLException, ClassNotFoundException {
        super.openDB();
        String sql = "SELECT * " +
                "FROM employee_inf AS ei JOIN dept_inf di on ei.DEPT_ID = di.ID JOIN job_inf ji on ji.ID = ei.JOB_ID";
        ps = con.prepareStatement(sql);
        rs = ps.executeQuery();
        List<TemployeeVO> temployeeVOs = new LinkedList<>();
        while (rs.next()) {
            temployeeVOs.add(conventRStoVO(rs));
        }
        return temployeeVOs;
    }

    @Override
    public List<TemployeeVO> getPart(Temployee temployee) throws SQLException, ClassNotFoundException {
        super.openDB();
        String sql = "SELECT * FROM employee_inf ei JOIN dept_inf di ON ei.DEPT_ID = di.ID JOIN job_inf ji ON ei.JOB_ID = ji.ID " +
                "WHERE ei.JOB_ID LIKE ? AND ei.NAME LIKE ? AND ei.CARD_ID LIKE ? AND ei.SEX LIKE ? AND ei.PHONE LIKE ? " +
                "AND ei.DEPT_ID LIKE ?";
        ps = con.prepareStatement(sql);
        if (temployee.getJobId() == 0) {
            ps.setString(1, "%");
        } else {
            ps.setInt(1, temployee.getJobId());
        }
        ps.setString(2, Util.likeStr(temployee.getName()));
        ps.setString(3, Util.likeStr(temployee.getCardId()));
        if (temployee.getSex() == 0) {
            ps.setString(4, "%");
        } else {
            ps.setInt(4, temployee.getSex());
        }
        ps.setString(5, Util.likeStr(temployee.getPhone()));
        if (temployee.getDeptId() == 0) {
            ps.setString(6, "%");
        } else {
            ps.setInt(6, temployee.getDeptId());
        }

        rs = ps.executeQuery();
        List<TemployeeVO> temployeeVOS = new ArrayList<>();
        while (rs.next()) {
            temployeeVOS.add(conventRStoVO(rs));
        }
        return temployeeVOS;
    }

    @Override
    public TemployeeVO getOne(int id) throws SQLException, ClassNotFoundException {
        super.openDB();
        String sql = "SELECT * " +
                "FROM employee_inf AS ei JOIN dept_inf di on ei.DEPT_ID = di.ID JOIN job_inf ji on ji.ID = ei.JOB_ID " +
                "WHERE ei.ID = ?";
        ps = con.prepareStatement(sql);
        ps.setInt(1, id);
        rs = ps.executeQuery();
        return rs.next() ? conventRStoVO(rs) : null;
    }

    @Override
    public void updateEmployee(Temployee temployee) throws SQLException, ClassNotFoundException {
        super.openDB();
        String sql = "UPDATE employee_inf SET `NAME` = ? , CARD_ID = ? , SEX = ? , JOB_ID = ? , EDUCATION = ? ," +
                " EMAIL = ? , PHONE = ? , TEL = ? , PARTY = ? , QQ_NUM = ? , ADDRESS = ? , POST_CODE = ? ," +
                " BIRTHDAY = ? , RACE = ? , SPECIALITY = ? , HOBBY = ? , REMARK = ? , DEPT_ID = ? WHERE ID = ?";
        ps = conventTemployeeToPs(sql, temployee);
        ps.setInt(19, temployee.getId());
        ps.executeUpdate();
    }

    @Override
    public void deleteEmployee(int id) throws SQLException, ClassNotFoundException {
        super.openDB();
        String sql = "DELETE FROM employee_inf WHERE ID = ?";
        ps = con.prepareStatement(sql);
        ps.setInt(1, id);
        ps.executeUpdate();
    }

    @Override
    public void addEmployee(Temployee temployee) throws SQLException, ClassNotFoundException {
        super.openDB();
        String sql = "INSERT INTO " +
                "employee_inf(`NAME`, CARD_ID, SEX, JOB_ID, EDUCATION, EMAIL, PHONE, TEL, PARTY, QQ_NUM, ADDRESS," +
                " POST_CODE, BIRTHDAY, RACE, SPECIALITY, HOBBY, REMARK, DEPT_ID) " +
                "VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
        ps = conventTemployeeToPs(sql, temployee);
        ps.executeUpdate();
    }

    private PreparedStatement conventTemployeeToPs(String sql, Temployee temployee) throws SQLException {
        PreparedStatement ps = con.prepareStatement(sql);
        ps.setString(1, temployee.getName());
        ps.setString(2, temployee.getCardId());
        ps.setInt(3, temployee.getSex());
        ps.setInt(4, temployee.getJobId());
        ps.setString(5, temployee.getEducation());
        ps.setString(6, temployee.getEmail());
        ps.setString(7, temployee.getPhone());
        ps.setString(8, temployee.getTel());
        ps.setString(9, temployee.getParty());
        ps.setString(10, temployee.getQqNum());
        ps.setString(11, temployee.getAddress());
        ps.setString(12, temployee.getPostCode());
        ps.setDate(13, temployee.getBirthday() == null ?
                null : new java.sql.Date(temployee.getBirthday().getTime()));
        ps.setString(14, temployee.getRace());
        ps.setString(15, temployee.getSpeciality());
        ps.setString(16, temployee.getHobby());
        ps.setString(17, temployee.getRemark());
        ps.setInt(18, temployee.getDeptId());
        return ps;
    }

    private TemployeeVO conventRStoVO(ResultSet rs) throws SQLException {
        TemployeeVO temployeeVO = new TemployeeVO();
        temployeeVO.setId(rs.getInt("ei.ID"));
        temployeeVO.setDeptName(rs.getString("di.NAME"));
        temployeeVO.setJobName(rs.getString("ji.NAME"));
        temployeeVO.setName(rs.getString("ei.NAME"));
        temployeeVO.setCardId(rs.getString("CARD_ID"));
        temployeeVO.setAddress(rs.getString("ADDRESS"));
        temployeeVO.setPostCode(rs.getString("POST_CODE"));
        temployeeVO.setTel(rs.getString("TEL"));
        temployeeVO.setPhone(rs.getString("PHONE"));
        temployeeVO.setQqNum(rs.getString("QQ_NUM"));
        temployeeVO.setEmail(rs.getString("EMAIL"));
        temployeeVO.setSex(rs.getInt("SEX") == 1 ? "男" : "女");
        temployeeVO.setParty(rs.getString("PARTY"));
        temployeeVO.setBirthday(rs.getDate("BIRTHDAY") == null ?
                null : new SimpleDateFormat("yyyy-MM-dd").format(rs.getDate("BIRTHDAY")));
        temployeeVO.setRace(rs.getString("RACE"));
        temployeeVO.setEducation(rs.getString("EDUCATION"));
        temployeeVO.setSpeciality(rs.getString("SPECIALITY"));
        temployeeVO.setHobby(rs.getString("HOBBY"));
        temployeeVO.setRemark(rs.getString("REMARK"));
        temployeeVO.setCreateDate(rs.getDate("CREATE_DATE"));
        return temployeeVO;
    }
}
